package com.alianzamedica.models;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.ArrayList;

import org.objectsearch.sqlsearch.ObjectSearch;
import org.w3c.dom.Document;

import com.alianzamedica.businessobject.Drug;
import com.alianzamedica.businessobject.Produit;
import com.alianzamedica.connection.ConnectionImpl;
import com.alianzamedica.tools.Enviroment;

/**
 * @author carlos
 * 
 */
public class DrugSelectModel {

	/**
	 * regresa los productos asociados al id de un doctor.
	 * 
	 * @param doctorId
	 *            id del doctor al cual estan asignados los productos.
	 * @return lista de productos.
	 */
	@SuppressWarnings("unchecked")
	public ArrayList getProducts(Integer doctorId) {
		ArrayList list = new ArrayList();
		String sql = "select dr.* from drug dr join produit "
				+ " p on (dr.num_produit=p.num_produit) join "
				+ " cfg_ligne_prix clp on ( p.num_produit = clp.num_produit  ) "
				+ " join machine m on ( m.num_platine = clp.num_platine  ) "
				+ " join site s on ( s.num_site = m.num_site ) "
				+ " join doctor d on ( d.Num_site = s.Num_site ) "
				+ " where  d.doctor_id = ?   and p.num_produit <> 0  "
				+ " and clp.Capacite> 0  order by p.num_produit";
		ConnectionImpl impl = new ConnectionImpl();
		Enviroment env = Enviroment.getInstance();
		Document doc = env.getDocument();
		ObjectSearch search = new ObjectSearch(doc, impl);
		PreparedStatement psmt = null;
		try {
			impl.open();
			Connection con = impl.getConnection();
			psmt = con.prepareStatement(sql);
			psmt.setInt(1, doctorId);
			list = search.customQuery(psmt, con, Drug.class);
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			impl.closeGracefully(psmt);
			try {
				impl.close();
			} catch (Exception e) {
			}
		}
		return list;
	}

	/**
	 * @param doctorId
	 * @param busqueda
	 * @return
	 */
	public ArrayList getProducts(int doctorId, String busqueda) {
		ArrayList list = new ArrayList();
		String sql = "select dr.* from drug dr join produit "
				+ " p on (dr.num_produit=p.num_produit) join "
				+ " cfg_ligne_prix clp on ( p.num_produit = clp.num_produit  ) "
				+ " join machine m on ( m.num_platine = clp.num_platine  ) "
				+ " join site s on ( s.num_site = m.num_site ) "
				+ " join doctor d on ( d.Num_site = s.Num_site ) "
				+ " where  d.doctor_id = ?   and p.num_produit <> 0  "
				+ " and lower(dr.DRUG_NAME) like ? "
				+ " and clp.Capacite> 0  order by p.num_produit";
		ConnectionImpl impl = new ConnectionImpl();
		Enviroment env = Enviroment.getInstance();
		Document doc = env.getDocument();
		ObjectSearch search = new ObjectSearch(doc, impl);
		PreparedStatement psmt = null;
		try {
			impl.open();
			Connection con = impl.getConnection();
			psmt = con.prepareStatement(sql);
			psmt.setInt(1, doctorId);
			String busquedaSQL=String.format("%s%s%s", '%',busqueda, '%');
			psmt.setString(2, busquedaSQL.toLowerCase());
			list = search.customQuery(psmt, con, Drug.class);
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			impl.closeGracefully(psmt);
			try {
				impl.close();
			} catch (Exception e) {
			}
		}
		return list;
	}

}
